package news.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import school.util.DBUtil;
import news.entity.NewsManage;

public class NewsDao {
	public static final NewsDao DAO = new NewsDao();

	/**
	 * 新闻分页
	 */
	public List<NewsManage> pageSearch(int pageSize, int pageNo) {
		List<NewsManage> li = new ArrayList<NewsManage>();
		int startpage = (pageNo - 1) * pageSize;
		int index = 1;
		ResultSet re = null;
		PreparedStatement pps = null;
		NewsManage s = null;

		String sql = "select * from news  order by pub_date desc limit ?,?";
		Connection con = DBUtil.getConnection();
		try {
			pps = con.prepareStatement(sql);
			pps.setInt(index++, startpage);
			pps.setInt(index++, pageSize);
			re = pps.executeQuery();
			while (re.next()) {
				s = new NewsManage();
				s.setNid(re.getInt("id"));
				// s.setNtid(re.getInt("cate_id"));
				s.setNtitel(re.getString("title"));

				s.setNcreatedate(re.getTimestamp("pub_date"));
				// s.setNpicpath(re.getString(6));
				// s.setNcontent(re.getString(7));
				// s.setNmiodifydate(re.getDate(8));
				// s.setNsummary(re.getString(9));
				li.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			// throw
		} finally {
			DBUtil.close(re, pps, con);
		}
		return li;

	}

	/**
	 * 按新闻编号查找新闻
	 * 
	 * @param newsid
	 * @return
	 */
	public NewsManage find(long newsid) {
		ResultSet re = null;
		PreparedStatement pps = null;
		NewsManage s = null;
		String sql = "select * from news where nid=?";
		Connection con = DBUtil.getConnection();
		try {
			pps = con.prepareStatement(sql);
			pps.setLong(1, newsid);
			re = pps.executeQuery();
			if (re.next()) {
				s = new NewsManage();
				s.setNid(re.getInt(1));
				s.setNtid(re.getInt(2));
				s.setNtitel(re.getString(3));
				s.setNauthor(re.getString(4));
				s.setNcreatedate(re.getDate(5));
				s.setNpicpath(re.getString(6));
				s.setNcontent(re.getString(7));
				s.setNmiodifydate(re.getDate(8));
				s.setNsummary(re.getString(9));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(re, pps, con);
		}
		return s;

	}

	/**
	 * 新增新闻
	 * 
	 * @param c
	 * @return
	 */
	public int add(NewsManage c) {
		Connection con = null;
		PreparedStatement ps = null;
		int re = 0;
		int index = 0;
		try {
			con = DBUtil.getConnection();
			String sql = "insert into news( nid,  tid,  ntitel,  nauthor,ncreatedate,  npicpath,  ncontent, nmiodifydate, nsummary ) values(?,?,?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setLong(index++, c.getNid());
			ps.setLong(index++, c.getNtid());
			ps.setString(index++, c.getNtitel());
			ps.setString(index++, c.getNauthor());
			ps.setDate(index++, (Date) c.getNcreatedate());
			ps.setString(index++, c.getNpicpath());
			ps.setString(index++, c.getNcontent());
			ps.setDate(index++, (Date) c.getNmiodifydate());
			ps.setString(index++, c.getNsummary());
			re = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return re;
	}

	/**
	 * 根据序号删除
	 */
	public int del(long num) {
		int index = 1;
		Connection con = null;
		PreparedStatement ps = null;
		int r = 0;
		con = DBUtil.getConnection();
		String sql = "delete from news where nid=? ";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(index++, num);
			r = ps.executeUpdate();
			System.out.println("删除了" + r + "条");
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return 0;

	}

	/**
	 * 根据新闻种类序号查找类别的前5条新闻信息
	 * 
	 * @param ntid
	 * @return
	 */
	public List<NewsManage> findTop5(long ntid) {
		ResultSet re = null;
		PreparedStatement pps = null;
		List<NewsManage> li = new ArrayList<NewsManage>();
		NewsManage s = null;
		String sql = "SELECT * FROM news WHERE ntid=? LIMIT 5";
		Connection con = DBUtil.getConnection();
		try {
			pps = con.prepareStatement(sql);
			pps.setLong(1, ntid);
			re = pps.executeQuery();
			while (re.next()) {
				s = new NewsManage();
				s.setNid(re.getInt(1));
				s.setNtid(re.getInt(2));
				s.setNtitel(re.getString(3));
				s.setNauthor(re.getString(4));
				s.setNcreatedate(re.getDate(5));
				s.setNpicpath(re.getString(6));
				s.setNcontent(re.getString(7));
				s.setNmiodifydate(re.getDate(8));
				s.setNsummary(re.getString(9));
				li.add(s);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(re, pps, con);
		}
		return li;

	}
}
